package cn.com.shopec.erp.common.excel;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public abstract class ExcelBaseImport {

	public static String XLS = "xls";
	public static String XLSX = "xlsx";
	public int beginRowIndex = 0;
	private Workbook workbook = null;

	/**
	 * 构造方法1（从第0行开始读数据）
	 * @param inputStream
	 * @param fileName
	 * @throws IOException
	 */
	public ExcelBaseImport(InputStream inputStream, String fileName) throws IOException {
		this.setWorkbook(inputStream,fileName);
	}

	/**
	 * 构造方法2（从指定行开始读取数据）
	 * @param inputStream
	 * @param fileName
	 * @param beginRowIndex
	 * @throws IOException
	 */
	public ExcelBaseImport(InputStream inputStream, String fileName, int beginRowIndex) throws IOException {
		this.beginRowIndex = beginRowIndex;
		this.setWorkbook(inputStream,fileName);
	}

	/**
	 * 设置workbook
	 * @param inputStream
	 * @param fileName
	 * @throws IOException
	 */
	private void setWorkbook(InputStream inputStream,String fileName) throws IOException {
		String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
		if (suffix.equals(XLS)) {
			this.workbook = new HSSFWorkbook(inputStream);
		} else {
			this.workbook = new XSSFWorkbook(inputStream);
		}
	}

	/**
	 * 将excel读取成List<List<Object>>形式数据
	 * @return
	 */
	public List<List<Object>> getObjectList() {
		Sheet sheet = this.workbook.getSheetAt(0);
		List<List<Object>> sheetData = new ArrayList<List<Object>>();
		int rowCount = sheet.getPhysicalNumberOfRows();
		for (int i = this.beginRowIndex; i < rowCount; i++) {
			List<Object> row = getRowObject(sheet.getRow(i));
			if (row != null) {
				sheetData.add(row);
			}
		}
		return sheetData;
	}

	/**
	 * 读取每行数据，并且封装成List<Object>，集合下标与excel中列从左到右一一对应
	 * @param row
	 * @return
	 */
	protected List<Object> getRowObject(Row row) {
		List<Object> rowData = null;
		if (row != null) {
			rowData = new ArrayList<Object>();
			for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
				Object cell = getCellData(row.getCell(i));
				rowData.add(cell);
			}
		}
		return rowData;
	}

	/**
	 * 读取excel单元格，并且转化指定格式
	 * @param cell
	 * @return
	 */
	protected Object getCellData(Cell cell) {
		Object cellData = null;
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					cellData = cell.getDateCellValue();
				} else {
					cellData = cell.getNumericCellValue();
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				cellData = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				cellData = cell.getBooleanCellValue();
				break;
			default:
				cellData = null;
				break;
			}
		}
		return cellData;
	}

	/**
	 * 将excel读取成List<Object>形式数据，其中Object取决于重写的setColumnMapping方法。
	 * 未重写时则无法使用该方法
	 * @return
	 */
	public List<Object> getList() {
		Sheet sheet = this.workbook.getSheetAt(0);
		List<Object> datas = new ArrayList<Object>();
		int rowCount = sheet.getPhysicalNumberOfRows();
		for (int i = this.beginRowIndex; i < rowCount; i++) {
			if(sheet.getRow(i)!=null){
				Object data = setColumnMapping(sheet.getRow(i));
				if (data != null) {
					datas.add(data);
				}
				
			}
		}
		return datas;
	}

	/**
	 * 设置excel行中每一列数据与方法返回类型java对象的映射关系
	 * @param row
	 * @return
	 */
	protected abstract Object setColumnMapping(Row row);
}
